using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using Peradigm.Framework.Domain.Exceptions;
using Peradigm.Framework.Domain.Parties;
using Peradigm.Framework.Domain.Parties.Exceptions;
using Peradigm.Framework.Persistence;
using Peradigm.Framework.Persistence.Exceptions;

using Liberty.Domain.ApplicationManagement;
using Liberty.Domain.CaseManagement;
using Liberty.Domain.Kernel;
using Liberty.Domain.ServiceMemberManagement;

using Liberty.Persistence.GrantManagement;

namespace Liberty.Persistence.Kernel
{
    public class PersonBroker : AbstractBroker
    {
        #region Constants
#warning Move these to public constants on the AbstractBroker class
        private const string PARAMETER_PREFIX = "@";
        private const string FIELD_OID = "OID";
        private const string FIELD_ROWVERSION = "Version";
        private const string SP_GET_ALL_RANKS = "[ServiceMemberManagement].[GetAllMilitaryRanks]";

        private const string
            SP_APPLICANTWITH = "Kernel.ApplicantWith",
            SP_PERSONWITH = "Kernel.PersonWith",
            SP_SERVICEMEMBERWITH = "Kernel.ServiceMemberWith",
            SP_SAVE_APPLICANT = "Kernel.SaveApplicant",
            SP_SAVE_PERSON = "Kernel.SavePerson",
            SP_SAVE_SERVICEMEMBER = "Kernel.SaveServiceMember",
            SP_RELATIONSHIP_TYPES = "[CaseManagement].[GetAllTypesOfKinship]";

#warning Reconcile ServiceMemberID and ServiceMemberOID
        private const string
            FIELD_SERVICE_MEMBER_ID = "ServiceMemberID",
            FIELD_SERVICEMEN_OID = "ServiceMenOID",
            FIELD_SERVICEMEN_VERSION = "ServiceMenVersion",
            FIELD_CURRENT_ADDRESS_ID = "ServiceMenPrimaryAddressID",
            FIELD_HOME_OF_RECORD_ADDRESS_ID = "ServiceMenPermanentAddressID",
            FIELD_FIRST_NAME = "FirstName",
            FIELD_LAST_NAME = "LastName",
            FIELD_MIDDLE_NAME = "MiddleInitial",
            FIELD_DOB = "DOB",
            FIELD_SEX = "Sex",
            FIELD_PRIMARY_PHONE = "Phone1",
            FIELD_SECONDARY_PHONE = "Phone2",
            FIELD_EMAIL_ADDRESS = "eMailAddress",
            FIELD_KINSHIP_ID = "KinshipID",
            FIELD_ADDRESSID = "AddressID",
            FIELD_APPLICANT_ID = "ApplicantID",
            FIELD_APPLICANT_ROWVERSION = "ApplicantVersion",
            FIELD_BRANCH_OF_SERVICE_ID = "BranchOfServiceID",
            FIELD_BRANCH_VERSION = "BranchVersion",
            FIELD_BRANCH = "Branch",
            FIELD_UNIT_ASSIGNMENT = "UnitAssignment",
            FIELD_RANK_ID = "RankID",
            FIELD_RANK_VERSION = "RankVersion",
            FIELD_RANK_PAY_GRADE = "RankPayGrade",
            FIELD_RANK_ABBREVIATION = "RankAbbreviation",
            FIELD_RANK = "Rank",
            FIELD_RANK_STRUCTURE = "RankStructure",
            FIELD_PERSON_OID = "PersonOID";

        private const string
            APPLICANT_CONTEXT_OID = "Applicant.OID",
            PERSON_CONTEXT_OID = "People.OID",
            SERVICEMEMBER_CONTEXT_OID = "ServiceMember.OID";

        private const string
            ERROR_SAVE_APPLICANT = "Unable to save applicant",
            ERROR_SAVE_PERSON = "Unable to save person",
            ERROR_SAVE_SERVICEMEMBER = "Unable to save service member.";

        #endregion

        #region Event Handlers
        #endregion

        #region Methods
        /// <summary>
        /// Answer an applicant with the specified ID.
        /// </summary>
        /// <param name="applicantID">
        /// The OID of the applicant to find.
        /// </param>
        /// <returns>
        /// An instance of Applicant
        /// </returns>
        virtual public Applicant ApplicantWith( long applicantID )
        {
            return this.ApplicantWith( applicantID, null );
        }

        /// <summary>
        /// Answer an applicant with the specified ID.
        /// </summary>
        /// <param name="applicantID">
        /// The OID of the applicant to find.
        /// </param>
        /// <param name="serviceMember">
        /// The service member that's related to the applicant.
        /// </param>
        /// <returns>
        /// An instance of Applicant
        /// </returns>
        virtual public Applicant ApplicantWith( long applicantID, ServiceMember serviceMember )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;
            Applicant applicant = null;

            try
            {
                cmd = this.CommandFor( SP_APPLICANTWITH );

                SqlParameter param = new SqlParameter( PARAMETER_PREFIX + FIELD_APPLICANT_ID, applicantID );
                cmd.Parameters.Add( param );

                reader = this.ExecuteReader( cmd );
                if( reader.Read() )
                {
                    applicant = this.ApplicantFrom( reader, serviceMember );
                }
            }
            catch( Exception )
            {
                throw;
            }
            finally
            {
                this.Close( cmd );
                this.Close( reader );
            }

            return applicant;
        }

        /// <summary>
        /// Answer a person with the specified ID.
        /// </summary>
        /// <param name="personID">
        /// The OID of the person to find.
        /// </param>
        /// <returns>
        /// An instance of Person
        /// </returns>
        virtual public Person PersonWith( long personID )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;
            Person person = null;

            try
            {
                cmd = this.CommandFor( SP_PERSONWITH );

                SqlParameter param = new SqlParameter( PARAMETER_PREFIX + FIELD_PERSON_OID, personID );
                cmd.Parameters.Add( param );

                reader = this.ExecuteReader( cmd );
                if( reader.Read() )
                {
                    person = this.PersonFrom( reader );
                }
            }
            catch( Exception )
            {
                throw;
            }
            finally
            {
                this.Close( cmd );
                this.Close( reader );
            }

            return person;
        }

        /// <summary>
        /// Answer a collection of people that match the specified criteria.
        /// </summary>
        /// <param name="criteria">
        /// Search criteria against which to match the people.
        /// </param>
        /// <returns>
        /// A collection of people.
        /// </returns>
        virtual public List<Person> PersonsFor( PersonSearchCriteria criteria )
        {

            SqlCommand cmd = null;
            SafeReader reader = null;
            List<Person> people = new List<Person>();

            try
            {
                cmd = this.CommandFor( criteria.CommandName );
                foreach( SqlParameter parameter in criteria.Parameters )
                {
                    cmd.Parameters.Add( parameter );
                }

                reader = this.ExecuteReader( cmd );
                while( reader.Read() )
                {
                    people.Add( this.PersonFrom( reader ) );
                }
            }
#warning Add exception handling.
            catch( Exception e )
            {
                throw;
            }
            finally
            {
                this.Close( reader );
                this.Close( cmd );
            }

            return people;
        }

        virtual public ServiceMember ServiceMemberWith( long serviceMemberID )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;
            ServiceMember serviceMember = null;

            try
            {
                cmd = this.CommandFor( SP_SERVICEMEMBERWITH );

                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_SERVICE_MEMBER_ID, serviceMemberID ) );

                reader = this.ExecuteReader( cmd );
                if( reader.Read() )
                {
                    serviceMember = this.ServiceMemberFrom( reader );
                }
            }
            finally
            {
                this.Close( cmd );
                this.Close( reader );
            }

            return serviceMember;
        }

        virtual public List<ServiceMember> ServiceMembersMatching( ServiceMemberSearchCriteria criteria )
        {
            List<ServiceMember> serviceMembers = new List<ServiceMember>();

            SqlCommand cmd = null;
            SafeReader reader = null;

            try
            {
                cmd = this.CommandFor( criteria.CommandName );

                foreach( SqlParameter parameter in criteria.Parameters )
                {
                    cmd.Parameters.Add( parameter );
                }

                reader = this.ExecuteReader( cmd );
                while( reader.Read() )
                {
                    serviceMembers.Add( this.ServiceMemberFrom( reader ) );
                }
            }
            finally
            {
                this.Close( reader );
                this.Close( cmd );
            }

            return serviceMembers;
        }

        public ServiceMember Save( ServiceMember serviceMember )
        {
            SqlTransaction txn = this.NewTransaction();
            PersonBroker personBroker;

            try
            {
                personBroker = new PersonBroker( txn );
                personBroker.SaveServiceMember( serviceMember );

                this.Commit( txn );
            }
            catch( EnterpriseException ex )
            {
                this.Rollback( txn );
                ex.AddContextItem( SERVICEMEMBER_CONTEXT_OID, serviceMember.Oid.ToString() );
                throw ex;
            }
            catch( Exception e )
            {
                this.Rollback( txn );
                ObjectNotSavedException onse = new ObjectNotSavedException( ERROR_SAVE_SERVICEMEMBER, e );
                if( serviceMember != null )
                {
                    onse.AddContextItem( SERVICEMEMBER_CONTEXT_OID, serviceMember.Oid.ToString() );
                }
                throw onse;
            }

            return serviceMember;
        }

        public Applicant Save( Applicant applicant )
        {
            SqlTransaction txn = this.NewTransaction();
            PersonBroker personBroker;

            try
            {
                personBroker = new PersonBroker( txn );
                personBroker.SaveApplicant( applicant );

                this.Commit( txn );
            }
            catch( EnterpriseException ex )
            {
                this.Rollback( txn );
                ex.AddContextItem( APPLICANT_CONTEXT_OID, applicant.Oid.ToString() );
                throw ex;
            }
            catch( Exception e )
            {
                this.Rollback( txn );
                ObjectNotSavedException onse = new ObjectNotSavedException( ERROR_SAVE_APPLICANT, e );
                if( applicant != null )
                {
                    onse.AddContextItem( APPLICANT_CONTEXT_OID, applicant.Oid.ToString() );
                }
                throw onse;
            }

            return applicant;
        }

        public Person Save( Person person )
        {
            SqlTransaction txn = this.NewTransaction();
            PersonBroker personBroker;

            try
            {
                personBroker = new PersonBroker( txn );
                personBroker.SavePerson( person );

                this.Commit( txn );
            }
            catch( EnterpriseException ex )
            {
                this.Rollback( txn );
                ex.AddContextItem( PERSON_CONTEXT_OID, person.Oid.ToString() );
                throw ex;
            }
            catch( Exception e )
            {
                this.Rollback( txn );
                ObjectNotSavedException onse = new ObjectNotSavedException( ERROR_SAVE_PERSON, e );
                if( person != null )
                {
                    onse.AddContextItem( PERSON_CONTEXT_OID, person.Oid.ToString() );
                }
                throw onse;
            }

            return person;
        }

        #endregion

        #region Properties
        #endregion

        #region Private Methods
        private RelationshipType RelationshipTypeWith( long oid )
        {
            ReferenceValueBroker<RelationshipType> broker = new ReferenceValueBroker<RelationshipType>( base.ConnectionString, SP_RELATIONSHIP_TYPES );
            return broker.ValueWith( oid );
        }

        internal Applicant ApplicantFrom( SafeReader reader, ServiceMember serviceMember )
        {
            Applicant applicant = new Applicant( reader.GetInt64( FIELD_OID ),
                                                 reader.GetRowVersion( FIELD_APPLICANT_ROWVERSION ),
                                                 reader.GetString( FIELD_FIRST_NAME ),
                                                 reader.GetString( FIELD_LAST_NAME ) );

            // Get the address.
            long addressID = reader.GetInt64( FIELD_ADDRESSID );
            applicant.Address = this.AddressBroker.AddressWith( addressID );
            applicant.PrimaryPhoneNumber = new UnvalidatedPhoneNumber( reader.GetString( FIELD_PRIMARY_PHONE ) );
            applicant.SecondaryPhoneNumber = new UnvalidatedPhoneNumber( reader.GetString( FIELD_SECONDARY_PHONE ) );
            applicant.EmailAddress = new UnvalidatedEMailAddress( reader.GetString( FIELD_EMAIL_ADDRESS ) );

            if( serviceMember != null )
            {
                // Setup the relationship with the service member.
                long kinshipID = reader.GetInt64( FIELD_KINSHIP_ID );
                applicant.RelateTo( this.RelationshipTypeWith( kinshipID ), serviceMember );
            }

            return applicant;
        }
        internal Person PersonFrom( SafeReader reader )
        {
            Person person;

            long oid = reader.GetInt64( FIELD_OID );
            byte[] version = reader.GetRowVersion( FIELD_ROWVERSION );
            string firstName = reader.GetString( FIELD_FIRST_NAME );
            string lastName = reader.GetString( FIELD_LAST_NAME );

            person = new Person( oid, version, firstName, lastName );

            return person;
        }
        private Applicant SaveApplicant( Applicant applicant )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;

            try
            {
                applicant.Address = i_AddressBroker.Save( applicant.Address );

                cmd = this.CommandFor( SP_SAVE_APPLICANT );

                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_OID, applicant.Oid ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_ROWVERSION, applicant.Version ) );

                if( applicant.Relationships.Count != 1 )
                {
                    throw new ObjectNotSavedException( "Applicant has no relationships defined" );
                }
                Relationship relationship = (Relationship)applicant.Relationships[0];
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_KINSHIP_ID, relationship.Type.Oid ) );

                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_ADDRESSID, applicant.Address.Oid ) );

                // Add Person attributes.
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_FIRST_NAME, applicant.FirstName ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_LAST_NAME, applicant.LastName ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_MIDDLE_NAME, applicant.MiddleName ) );
                // cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_DOB, serviceMember.BornOn ) );
                // cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_SEX, person.??? ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_PRIMARY_PHONE, applicant.PrimaryPhoneNumber.AsString() ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_SECONDARY_PHONE, applicant.SecondaryPhoneNumber.AsString() ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_EMAIL_ADDRESS, applicant.EmailAddress.AsString() ) );

                // Execute and read oid/timestamp
                reader = this.ExecuteReader( cmd );
                if( reader.Read() )
                {
                    applicant.Oid = reader.GetInt64( FIELD_OID );
                    applicant.Version = reader.GetRowVersion( FIELD_ROWVERSION );
                }
                else
                {
                    throw new ObjectNotSavedException( ERROR_SAVE_APPLICANT );
                }
            }
            finally
            {
                this.Close( reader );
                this.Close( cmd );
            }

            return applicant;
        }

        private Person SavePerson( Person person )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;

            try
            {
                cmd = this.CommandFor( SP_SAVE_PERSON );

                SqlParameter paramOID = new SqlParameter( PARAMETER_PREFIX + FIELD_OID, person.Oid );
                paramOID.Direction = ParameterDirection.InputOutput;
                cmd.Parameters.Add( paramOID );
                SqlParameter paramVersion = new SqlParameter( PARAMETER_PREFIX + FIELD_ROWVERSION, person.Version );
                paramVersion.Direction = ParameterDirection.InputOutput;
                cmd.Parameters.Add( paramVersion );

                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_FIRST_NAME, person.FirstName ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_LAST_NAME, person.LastName ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_MIDDLE_NAME, person.MiddleName ) );
                // cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_DOB, serviceMember.BornOn ) );
                // cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_SEX, person.??? ) );
                // cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_PRIMARY_PHONE, serviceMember.PrimaryPhoneNumber.AsUnformattedString() ) );
                // cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_SECONDARY_PHONE, serviceMember.SecondaryPhoneNumber.AsUnformattedString() ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_EMAIL_ADDRESS, person.EmailAddressFor( TypeOfContactPoint.DESCRIPTOR_HOME ) ) );

                // Execute and read oid/timestamp
                this.ExecuteNonQuery( cmd );

                person.Oid = (long)paramOID.Value;
                person.Version = (byte[])paramVersion.Value;
            }
            finally
            {
                this.Close( reader );
                this.Close( cmd );
            }

            return person;
        }

        private ServiceMember SaveServiceMember( ServiceMember serviceMember )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;

            try
            {
                serviceMember.CurrentAddress = i_AddressBroker.Save( serviceMember.CurrentAddress );
                serviceMember.HomeOfRecord = i_AddressBroker.Save( serviceMember.HomeOfRecord );

                cmd = this.CommandFor( SP_SAVE_SERVICEMEMBER );

                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_OID, serviceMember.Oid ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_ROWVERSION, serviceMember.Version ) );

                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_CURRENT_ADDRESS_ID, serviceMember.CurrentAddress.Oid ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_HOME_OF_RECORD_ADDRESS_ID, serviceMember.HomeOfRecord.Oid ) );

                // Add Person attributes.
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_FIRST_NAME, serviceMember.FirstName ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_LAST_NAME, serviceMember.LastName ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_MIDDLE_NAME, serviceMember.MiddleName ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_DOB, serviceMember.BornOn ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_PRIMARY_PHONE, serviceMember.PrimaryPhoneNumber.AsUnformattedString() ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_SECONDARY_PHONE, serviceMember.SecondaryPhoneNumber.AsUnformattedString() ) );

                // Service history.
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_BRANCH_OF_SERVICE_ID, serviceMember.Branch.Oid ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_RANK_ID, serviceMember.Rank.Oid ) );
                cmd.Parameters.Add( new SqlParameter( PARAMETER_PREFIX + FIELD_UNIT_ASSIGNMENT, serviceMember.UnitAssignment.Name ) );

                // Execute and read oid/timestamp
                reader = this.ExecuteReader( cmd );
                if( reader.Read() )
                {
                    serviceMember.Oid = reader.GetInt64( FIELD_OID );
                    serviceMember.Version = reader.GetRowVersion( FIELD_ROWVERSION );
                }
                else
                {
                    throw new ObjectNotSavedException( ERROR_SAVE_SERVICEMEMBER );
                }
            }
            finally
            {
                this.Close( reader );
                this.Close( cmd );
            }

            return serviceMember;
        }

        internal ServiceMember ServiceMemberFrom( SafeReader reader )
        {
            long oid = reader.GetInt64( FIELD_SERVICEMEN_OID );
            byte[] version = reader.GetRowVersion( FIELD_SERVICEMEN_VERSION );
            string firstName = reader.GetString( FIELD_FIRST_NAME );
            string lastName = reader.GetString( FIELD_LAST_NAME );
            DateTime dateOfBirth = reader.GetDateTime( FIELD_DOB );
            string sex = reader.GetString( FIELD_SEX );
            string emailAddress = reader.GetString( FIELD_EMAIL_ADDRESS );
            long branchOfServiceID = reader.GetInt64( FIELD_BRANCH_OF_SERVICE_ID );
            byte[] branchVersion = reader.GetRowVersion( FIELD_BRANCH_VERSION );
            string branchDescription = reader.GetString( FIELD_BRANCH );
            string unitAssignment = reader.GetString( FIELD_UNIT_ASSIGNMENT );
            long rankID = reader.GetInt64( FIELD_RANK_ID );
            Unit unit = new Unit( unitAssignment );

            ServiceBroker sb = new ServiceBroker( this.ConnectionString );
            Branch branch = sb.ServiceBranchWith( branchOfServiceID );
            Rank rank = sb.RankWith( rankID );

            ServiceMember serviceMember = new ServiceMember( oid, version, firstName, lastName, dateOfBirth, rank, unit );

            this.AddContactPointsFrom( serviceMember, reader );

            return serviceMember;
        }

        internal void AddContactPointsFrom( Person person, SafeReader reader )
        {
            if ( !reader.IsDBNull( FIELD_CURRENT_ADDRESS_ID ) )
            {
                long addressID = reader.GetInt64( FIELD_CURRENT_ADDRESS_ID );
                Liberty.Domain.Kernel.Address address = i_AddressBroker.AddressWith( addressID );
                person.AddAddress( ServiceMember.CURRENT_ADDRESS, address );
            }
            if ( !reader.IsDBNull( FIELD_HOME_OF_RECORD_ADDRESS_ID ) )
            {
                long addressID = reader.GetInt64( FIELD_HOME_OF_RECORD_ADDRESS_ID );
                Liberty.Domain.Kernel.Address address = i_AddressBroker.AddressWith( addressID );
                person.AddAddress( ServiceMember.HOME_OF_RECORD_ADDRESS, address );
            }
            string primaryPhone = reader.GetString( FIELD_PRIMARY_PHONE );
            person.AddPhoneNumber( ServiceMember.PRIMARY_PHONE_NUMBER, new UnvalidatedPhoneNumber( primaryPhone ) );
            string secondaryPhone = reader.GetString( FIELD_SECONDARY_PHONE );
            person.AddPhoneNumber( ServiceMember.SECONDARY_PHONE_NUMBER, new UnvalidatedPhoneNumber( secondaryPhone ) );
        }
        #endregion

        #region Private Properties
        private AddressBroker AddressBroker
        {
            get
            {
                return i_AddressBroker;
            }
            set
            {
                i_AddressBroker = value;
            }
        }
        #endregion

        #region Construction and Finalization
        public PersonBroker( SqlTransaction txn )
            : base( txn )
        {
            this.AddressBroker = new AddressBroker( txn );
        }

        public PersonBroker( string cxnString )
            : base( cxnString )
        {
            this.AddressBroker = new AddressBroker( cxnString );
        }
        #endregion

        #region Data Elements
        AddressBroker i_AddressBroker;
        #endregion
    }
}
